<?php


/**
 *  Antz First ever DB class!
 *  Provides handy functions for common tasks to MYSQL databases
 */ 
require_once('Select.php');
class Antz_Db_Adodb extends Antz_Db
{
    protected $conn;
    protected $res;
    protected $showErrors;
    protected $debug;
    protected $dbType = 'MySQL';
    
    public function __construct($host, $username, $pass, $dbName, $showErrors=false)
    {
        $this->adodb = adodb_connect();
        
        if($showErrors) $this->adodb->debug = true;
        
        $this->dbName = $dbName;
        $this->showErrors($showErrors);
  
    }
    
    /**
     *  Turn errors on or off
     */         
    public function showErrors($bool)
    {
        $this->showErrors = ($bool===true || $bool===false) ? $bool : false;
    }
    
    public function debug($bool)
    {
        $this->debug = ($bool === true || $bool === false) ? $bool : false;
    }

    
    public function stripSlashes($mixed)
    {
        if(is_array($mixed)){
            foreach($mixed as $k=>$v){
                $mixed[$k] = $this->stripSlashes($v);
            };
        }else{
            $mixed = stripslashes($mixed);
        };
        return $mixed;
    }
    
    /**
     *  Returns an escaped version. Make sure you escape everything you get from a user
     */         
    public function makeSafe($text)
    {
        global $gCms;
        $config = $gCms->config;
        if(is_array($text)){
            foreach($text as $k=>&$v){
                $v = $this->makeSafe($v);
            };
        }else{
            if($text === null) $text = 'null';
            if($config['dbms']=='mysql') return mysql_real_escape_string($text, $this->adodb->connectionId);
            else if($config['dbms']=='postgres7') return pg_escape_string($this->adodb->connectionId, $text);
            else return addslashes($text);
        };
    }
    
    public function escape($text){
        return $this->makeSafe($text);
    }
    
    /**
     *  Strips any unacceptable characters, leaving only character that are safe for keys
     */
    public function makeSafeKey($text)
    {
        return preg_replace('/[^0-9a-zA-Z\-_]/', '', $text);
    }         
    
    
    /**
     *  Number of rows that are in a result set
     */         
    public function numRows()
    {
        return @mysql_num_rows($this->res);
    }
    
    /**
     *  Number of rows that were affected by the last query
     */         
    public function affectedRows()
    {
        return @mysql_affected_rows($this->conn);
    }
    
    public function fetchRow($q='')
    {
        if($this->debug) echo $q.'<br />';
        return $this->adodb->GetRow($q);
    }
    
    public function select()
    {
        return Antz::newObject('Antz_Db_Select');
    }
    
    public function delete($tablename, $where)
   { // BEGIN function delete
   	    $this->query('DELETE FROM '.$tablename. ' WHERE '.$where);
   } // END function delete
    
    public function query($q)
    {
        if(is_object($q) && $q instanceof Antz_Db_Select) $q = $q->__toString();
        $q = str_replace("'null'", 'null', $q);
        if($this->debug) echo $q.'<br />';
        if(!$this->adodb->Execute($q)){
            if($this->showErrors) echo 'Error: '.$this->adodb->ErrorMsg().'<br />'.$q.'<br />';
            return false;
        };
        return true;
    }
    

    public function fetchAll($q)
    {
        if($this->debug) echo $q.'<br />';
        return $this->adodb->GetAll($q);
    }
    
    /**
     *  Inserts a new row in the db, returns insertId or false
     */         
    public function insert($table, $pairs, $idColName='id', $seqName='')
    {
        global $gCms;
        $config = $gCms->config;
        $pgSpecialId = false;
        
        if($config['dbms']=='mysql'){
            // inserting is much easier for mysql
            return $this->insertMysql($table, $pairs);
        };

        // now we make a special effort to insert to postgres, because it is like a child, you need to hold it's hand.
        
        if($seqName == '') $seqName = $table.'_'.$idColName.'_seq';
        
        if(!array_key_exists($idColName, $pairs) && $config['dbms']=='postgres7') $pgSpecialId = true;
        if($pgSpecialId){
            // id field is not explicitly set, find our auto_incrementing value
            $this->adodb->Execute('BEGIN WORK');

            if($insertId = $this->adodb->GenId($seqName)){
                $pairs[$idColName] = $insertId;
            }else{
                $pgSpecialId = false;
                $this->adodb->Execute('ROLLBACK');
            };
        };

        // the standard part - convert our parameters to a query
        $keys = array();
        $values = array();
        foreach($pairs as $k=>$v){
            $keys[] = $k;
            $values[] = pg_escape_string($v);
        };
        $keys = implode(', ', $keys);
        $values = implode("', '", $values);
        $q = "INSERT INTO {$table} ({$keys}) VALUES ('".$values."')";
        if($this->debug) echo 'Query: '.$q.'<br />';

        // give it a go, if it fails we return false
        if(false === ($this->res = $this->adodb->Execute($q))){
            if($this->showErrors) echo $this->adodb->ErrorMsg().'<br />'.$q.'<br />';
            if($pgSpecialId) $this->adodb->Execute('ROLLBACK');
            return false;
        };

        // check that our transaction went without a hitch and our id is actually the id we expect
        if($pgSpecialId){
            $q = "SELECT {$keys} FROM {$table} WHERE id = '{$insertId}'";
            $res = $this->adodb->GetRow($q);
            $res = $this->stripSlashes($res);
            $diff = array_diff($res, $pairs);
            if($res[$idColName] == $insertId){
                // success!
                $this->adodb->Execute('COMMIT');
                return $insertId;
            }else{
                // uh-oh, maybe a simultaneous insert has used our id, lets keep trying until it goes perfectly
                $this->adodb->Execute('ROLLBACK');
                unset($pairs[$idColName]);
                return $this->insert($table, $pairs, $idColName, $seqName);
            };
        }else{
            return NULL;
        };

        // we get here only if the id col value was specified in the pairs array, in which case we return the id we got given
        return $pairs[$idColName];
    }
    
    
    protected function insertMysql($table, $pairs){
        $keys = array();
        $values = array();
        foreach($pairs as $k=>$v){
            $keys[] = $k;
            $values[] = $this->adodb->qstr($v);
        };
        $keys = implode(', ', $keys);
        $values = implode(',', $values);
        $q = "INSERT INTO {$table} ({$keys}) VALUES (".$values.")";
        if($this->debug) echo $q.'<br />';
        if(false===($this->res = $this->adodb->Execute($q))){
              if($this->showErrors) echo $this->adodb->ErrorMsg().'<br />'.$q.'<br />';
              return false;            
        };
        return $this->adodb->Insert_ID();
    }
    
    
    
    public function update($tableName, $pairs, $where)
    {
        $qs = array();
        foreach($pairs as $k=>$v){
            $qs[] = $k." = '".$this->escape($v)."'";
        };
        $q = "UPDATE {$tableName} SET ".implode(',', $qs)." WHERE {$where}";
        //echo $q.'<br />';
        $this->query($q);

    }
    
    

    /**
     *  Count how many rows match a condition. Useful for checking if a result exists
     *  EG:
     *     if($DB->countRows('users', array("id = '$userId'")) > 0){
     *         // we know there is a user with the corresponding id
     *     }     
     */
             
    public function countRows($tableName, $params)
    {
        return (int) $this->oneValue($tableName, 'COUNT(*)', $params);
    }

    /**
     *  Allows you to get 1 value from the db in 1 step:
     *   EG:
     *     $title = $DB->oneValue('tableName', 'title', array("id = '$topicId'"));
     */
    public function oneValue($tableName, $field, $params)
    {
        $q = "SELECT {$field} FROM {$tableName} ";
     
        if(is_array($params)) $params = implode(' AND ', $params);
        if($params != ''){
            $q .= ' WHERE '.$params;
        };
        if($this->debug) echo $q.'<br />';
        return $this->adodb->GetOne($q);
    }               
    
    /**
     *  Allows one field to be incremented 
     */         
    public function incrementField($table, $field, $where, $amount=1)
    {
        $where = implode(' AND ', $where);
        $q = "UPDATE {$table} SET {$field} = ({$field} + {$amount}) WHERE {$where}";
        $this->query($q);
    }
    
}
